using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Diagnostics;

namespace CUtil.Excel
{
	/// <summary>
	/// Represents an Excel report.
	/// This is a very dead simple excel report showing sample prices data.
	/// </summary>
	public class ExcelWriter
	{
        StreamWriter streamWriter;
        string crlf = "\r\n";
		/// <summary>
		/// A simple way to provide sample data for the excel report.
		/// </summary>
		

		/// <summary>
		/// Instantiate a price report.
		/// </summary>
        public ExcelWriter()
		{
			
			//myXmlFile = @"C:\\prices.xml";
	
			

		} // constructor

		/// <summary>
		/// Generates the actual Excel report and saves to disk.
		/// </summary>

        public bool CloseFile()
        {
            streamWriter.WriteLine("</Workbook>" + crlf);
            streamWriter.Close();
            return true;

        }
        public bool CreateFile(string fileName)
        {
            FileInfo fi = new FileInfo(fileName);
            streamWriter = null;
            try
            {
                streamWriter = fi.CreateText();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("File is in use " + fileName + " ; " + ex.Message);
                System.IO.File.Delete(fileName);
                streamWriter = fi.CreateText();
                return false;
            }
            

            streamWriter.WriteLine(
                "<?xml version=\"1.0\"?>" + crlf +
                "<?mso-application progid=\"Excel.Sheet\"?>" + crlf +
                "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" + crlf +
                "xmlns:o=\"urn:schemas-microsoft-com:office:office\"" + crlf +
                "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"" + crlf +
                "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"" + crlf +
                "xmlns:html=\"http://www.w3.org/TR/REC-html40\">" + crlf +
                "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">" +
                "<Author>Ed</Author>" + crlf +
                "<LastAuthor>Ed</LastAuthor>" + crlf +
                //"<Created>2005-11-07T02:19:12Z</Created>
                "<Company>Ed.com</Company>" + crlf +
                "<Title><![CDATA[Eds " + System.DateTime.Now.ToString() + "]]></Title>" + crlf +
                "<Subject>Pricing Report</Subject>" + crlf +
                //"<Description>Some comments</Description>" + crlf +
                "<Version>11.6408</Version>" + crlf +
                "</DocumentProperties>");

            streamWriter.WriteLine(
                "<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">" + crlf +
                "<DownloadComponents/>" + crlf +
                //"<LocationOfComponents HRef=\"file:///D:\\\"/>" + crlf +
                "</OfficeDocumentSettings>" + crlf +
                "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">" + crlf +
                //<WindowHeight>11765</WindowHeight>
                //<WindowWidth>15446</WindowWidth>
                //<WindowTopX>217</WindowTopX>
                //<WindowTopY>95</WindowTopY>
                //<ProtectStructure>False</ProtectStructure>
                //<ProtectWindows>False</ProtectWindows>
                "</ExcelWorkbook>");

            streamWriter.WriteLine(
                "<Styles>" + crlf +
                "  <Style ss:ID=\"Default\" ss:Name=\"Normal\">" + crlf +
                "    <Alignment ss:Vertical=\"Bottom\"/>" + crlf +
                "    <Borders/>" + crlf +
                "    <Font/>" + crlf +
                "    <Interior/>" + crlf +
                "    <NumberFormat/>" + crlf +
                "    <Protection/>" + crlf +
                "  </Style>" + crlf +
                "  <Style ss:ID=\"BoldAndUnderline\">" + crlf +
                "    <Font x:Family=\"Swiss\" ss:Bold=\"1\" ss:Underline=\"Single\"/>" + crlf +
                "  </Style>" + crlf +
                "  <Style ss:ID=\"s26\">" + crlf +
                "    <Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Bottom\"/>" + crlf +
                "    <NumberFormat ss:Format=\"&quot;$&quot;#,##0.00\"/>" + crlf +
                "  </Style>" + crlf +
                "  <Style ss:ID=\"Date\">" + crlf +
                "    <NumberFormat ss:Format=\"[$-409]m/d/yy\\ h:mm\\ AM/PM;@\"/>" + crlf +
                "  </Style>" + crlf +
                "</Styles>" + crlf);



            return true;

        }

        public bool GenerateWorksheet(DataTable dataTable, string worksheetName)
        {
            Debug.Assert(dataTable != null, "DataTable is null");
            if (dataTable == null)
                return false;

            
            streamWriter.WriteLine(
                "<Worksheet ss:Name=\" " + worksheetName + "\">" + crlf +
                "<Table " +
                //ss:ExpandedColumnCount=\"3\" " +
                //"ss:ExpandedRowCount=\"1\" " +
                "x:FullColumns=\"1\"" + crlf +
                "x:FullRows=\"1\" ss:DefaultColumnWidth=\"48.905660377358487\"" + crlf +
                "ss:DefaultRowHeight=\"12.90566037735849\">" + crlf);

            streamWriter.WriteLine("<Column ss:Index=\"1\" ss:Width=\"100\"/>");     // Title
            streamWriter.WriteLine("<Column ss:Index=\"2\" ss:Width=\"100\" ss:StyleID=\"s26\"/>");   // Rate 
            streamWriter.WriteLine("<Column ss:Index=\"3\" ss:Width=\"100\" ss:StyleID=\"s26\"/>");   // Min charge
            streamWriter.WriteLine("<Column ss:Index=\"4\" ss:Width=\"100\" ss:StyleID=\"s26\"/>");   // Max charge
            streamWriter.WriteLine("<Column ss:Index=\"5\" ss:Width=\"100\" ss:StyleID=\"s26\"/>");   // data version
            streamWriter.WriteLine("<Column ss:Index=\"6\" ss:Width=\"100\" ss:StyleID=\"Date\"/>");   // date created
            streamWriter.WriteLine("<Column ss:Index=\"7\" ss:Width=\"100\" ss:StyleID=\"Date\"/>");   // date modified
            //------------------------------
            //// Column headers. 
            try
            {
                //streamWriter.WriteLine("<Row>");
                streamWriter.WriteLine("<Row ss:StyleID=\"BoldAndUnderline\">");
                
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    streamWriter.WriteLine("<Cell><Data ss:Type=\"String\">");
                    streamWriter.WriteLine(dataTable.Columns[i].ColumnName);
                    streamWriter.WriteLine("</Data></Cell>");


                }
                streamWriter.WriteLine("</Row>" + crlf);
                
            }
            catch (Exception ex)
            {
                Debug.Assert(false, "Error writing Excel columns: " + ex.Message);
            }
            // Loop over all rows
            //------------------------------
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                streamWriter.WriteLine("<Row>");
                
                DataRow dr = dataTable.Rows[i];
                for (int j = 0; j < dr.ItemArray.GetLength(0); j++)
                {
                    streamWriter.WriteLine("<Cell><Data ss:Type=\"String\">");
                    streamWriter.WriteLine(dr[j].ToString());
                    streamWriter.WriteLine("</Data></Cell>");
                }
                streamWriter.WriteLine("</Row>" + crlf);
            }
          
            streamWriter.WriteLine("</Table>" + crlf);

            streamWriter.WriteLine(
                "<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">" + crlf +
                "<Selected/>" + crlf +
                "<Panes>" + crlf +
                "<Pane>" + crlf +
                "<Number>3</Number>" + crlf +
                "<ActiveRow>1</ActiveRow>" + crlf +
                "</Pane>" + crlf +
                "</Panes>" + crlf +
                "<ProtectObjects>False</ProtectObjects>" + crlf +
                "<ProtectScenarios>False</ProtectScenarios>" + crlf +
                "</WorksheetOptions>" + crlf +
                "</Worksheet>");

            

            
            return true;
        } // GenerateReport
	}
}
